<#include "license.ftl">
<@license/>
<#macro list_fields>
"o${object.@sequence}.id"
<#if object.listScope[0]??>
    <#list object.listScope?children as att>
        <#if att?node_name != "object">
            + ",o${object.@sequence}.${att.@fieldName}"
        </#if>
        <#t>
    </#list>
</#if>
</#macro>
<#macro table_fields>
"o${object.@sequence}.id"
<#list object.tableScope?children as att>
    <#if att?node_name != "object">
            + ",o${object.@sequence}.${att.@fieldName}"
    </#if>
</#list>
            + ",o${object.@sequence}.creationDate,o${object.@sequence}.updateDate"
</#macro>
<#assign object = doc.object>
package ${object.@package}.sql.base;

/**
* All the generated SQL statements for ${object.@name} go in here.
* If you have custom queries, or you want to override existing ones, 
* best put them in here ${object.@package}.service.${object.@name}.
<#if object.trashcan == "true">
* <br>WARNING ${object.@name} IMPLEMENTS TRASHCAN!!<br>
* Therefore, remember to suffix every where clause with 'AND roDeleted=false'.
</#if>
* @author Redora (www.redora.net)
* @see ${object.@package}.sql.${object.@name}SQL
*/
public class ${object.@name}SQLBase {

    /**
    * Sequence of this model in the list of all models. This number is used
    * to create an unique alias for select queries and is can also be used to
    * get the table name from ${object.@package}.service.${object.@name}Service.OBJECTS[].
    * <br>
    * Do not use the number hard coded, it can change when the model changes. Reference this
    * or ALIAS instead.
    */
    public final static int SEQUENCE = ${object.@sequence};

    /** Table alias used for select queries.*/
    public final static String ALIAS = "o" + SEQUENCE;

    /**
    * The generated finder methods are added to this enum. Also the GWT client has
    * the same DefaultFinder, ordered in exactly the same manner. Therefor it is
    * possible to use the enum's ordinal position:
    * <code>
    * int ord = GwtClientObject.DefaultFinder.FindAll.ordinal();
    * if (ClientObjectSQL.DefaultFinder.values()[ord].name().equals("FindAll") {
    *    System.out.print("Hello World");
    * }
    * </code>
    */

    public enum DefaultFinder {
<#list doc["/object/finder[@name='findAll']"] as finder>
        FindAll(${finder.@listName}, ${finder.@tableName}, ${finder.@json})
</#list>
<#list doc["//attributes/*/finder"] as finder>
        , ${finder.@name?cap_first}(${finder.@listName}, ${finder.@tableName}, ${finder.@json})
</#list>
<#list doc["//queries/*/finder"] as finder>
    <#if finder?parent.@javadoc[0]??>
        /** ${finder?parent.@javadoc} */
    </#if>
        , ${finder.@name?cap_first}(${finder.@listName}, ${finder.@tableName}, ${finder.@json})
</#list>
        ;
        public final String sqlTable;
        public final String sqlList;
        public boolean allowJson;
        DefaultFinder(String sqlList, String sqlTable, boolean allowJson) {
            this.sqlTable = sqlTable;
            this.sqlList = sqlList;
            this.allowJson = allowJson;
        }
    }

    /** All the fields that are usually needed in the drop list as defined in the model.*/
    public final static String LIST_FIELDS = <@list_fields />;

    /** The most important fields used for a tabular layout.*/
    public final static String TABLE_FIELDS = <@table_fields />;

    /** All the fields except the related children.*/
    public final static String FORM_FIELDS = ALIAS + ".id," +
<#list object.formScope?children as att>
    <#if att?node_name != "object">
            ALIAS + ".${att.@fieldName}," +
    </#if>
</#list>
            ALIAS + ".creationDate," + ALIAS + ".updateDate";

<#if object.lazyScope[0]??>
    /** All the fields that are lazy loading as defined in the model.*/
    public final static String LAZY_FIELDS = ALIAS + ".id," +
    <#list object.lazyScope?children as att>
            ALIAS + ".${att.@fieldName}," +
        <#t>
    </#list>
            ALIAS + ".creationDate," + ALIAS + ".updateDate";
</#if>

    /** delete from ${object.@name} where id = ? */
    public final static String DELETE = "delete from `${object.@name}` where id = ?";

    /** default order for query */
    public final static String DEFAULT_ORDER = " order by "          <#assign orders = 0>
<#if object.defaultOrder[0]??>
        + "${object.defaultOrder?trim}".replace("<#noparse>${ALIAS}</#noparse>", "o${object.@sequence}");    <#assign orders = orders + 1>
<#elseif doc["/object/attributes/*[@sort]"][0]??>
    <#list doc["/object/attributes/*[@sort]"] as att>
        <#if orders != 0>+ ", " </#if>+ ALIAS + ".${att.@fieldName} ${att.@sort}"        <#assign orders = orders + 1>
    </#list>
    <#if orders != 0>;</#if>
<#elseif orders == 0 && object.listScope[0]??>
    <#list object.listScope?children as att>
        <#if att?node_name != "object">
        <#if orders != 0>+ ", " </#if>+ ALIAS + ".${att.@fieldName}"           <#assign orders = orders + 1>
        </#if>
    </#list>
    <#if orders != 0>;</#if>
</#if>
<#if orders == 0>
        + "id";
</#if>

<#if object.trashcan == "true">
    public final static String FIND_TRASH = 
        "select " + TABLE_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".roDeleted = true";
    public final static String FIND_TRASH_BY_ID = 
        "select " + FORM_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".roDeleted = true and " + ALIAS + ".id = ?";
    public final static String TRASH = 
        "update `${object.@name}` set roDeleted = ? where id = ?";

</#if>
    public final static String FIND_BY_ID_FORM =
            "select " + FORM_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
    public final static String FIND_BY_ID_TABLE =
            "select " + TABLE_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
    public final static String FIND_BY_ID_LIST =
            "select " + LIST_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id = ? <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
    public final static String FIND_IN_ID_FORM =
            "select " + FORM_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id in (?) <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
    public final static String FIND_IN_ID_TABLE =
            "select " + TABLE_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id in (?) <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";
    public final static String FIND_IN_ID_LIST =
            "select " + LIST_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".id in (?) <#if object.trashcan == "true">and " + ALIAS + ".roDeleted = false</#if>";

    public final static String FIND_ALL_TABLE = 
        "select " + TABLE_FIELDS + " from `${object.@name}` as " + ALIAS + " <#if object.trashcan == "true">where " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;
    public final static String FIND_ALL_LIST = 
        "select " + LIST_FIELDS + " from `${object.@name}` as " + ALIAS + " <#if object.trashcan == "true">where " + ALIAS + ".roDeleted = false </#if>" + DEFAULT_ORDER;


<#list doc["//queries/query"] as query>
    <#if query.@javadoc[0]??>
    /** ${query.@javadoc} */
    </#if>
    <#if query.finder[0]??>
    public final static String ${query.finder[0].@listName} =
        (<#if !query.list[0]?trim?starts_with("select")>"select distinct " + <@list_fields /> + <#if !query.list[0]?trim?starts_with("from")>" from `${object.@name}` as o${object.@sequence}" + </#if></#if>" ${query.list[0]?trim?replace('${', '" + ')?replace('}', ' + "')}")
        <#list query.replace as replace>
        .replace("${replace.@what}".replace("<#noparse>${ALIAS}</#noparse>", "o${object.@sequence}"), "${replace.@with}".replace("<#noparse>${ALIAS}</#noparse>", "o${object.@sequence}"))
        </#list>
    ;
    public final static String ${query.finder[0].@tableName} =
        (<#if !query.table[0]?trim?starts_with("select")>"select distinct " + <@table_fields /> + <#if !query.table[0]?trim?starts_with("from")>" from `${object.@name}` as o${object.@sequence}" + </#if></#if>" ${query.table[0]?trim?replace('${', '" + ')?replace('}', ' + "')}")
        <#list query.replace as replace>
        .replace("${replace.@what}".replace("<#noparse>${ALIAS}</#noparse>", "o${object.@sequence}"), "${replace.@with}".replace("<#noparse>${ALIAS}</#noparse>", "o${object.@sequence}"))
        </#list>
    ;
    <#else>
    public final static String ${query.@sqlName} = "${query.default?trim?j_string}";
    </#if>
</#list>

<#list doc["//attributes/*/finder[not(ancestor::set)]"] as finder>
    public static final String ${finder.@listName} =
            "select " + LIST_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".${finder?parent.@fieldName}<#if finder?parent?node_name == "object">Id</#if> = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
    public static final String ${finder.@tableName} =
            "select " + TABLE_FIELDS + " from `${object.@name}` as " + ALIAS + " where " + ALIAS + ".${finder?parent.@fieldName}<#if finder?parent?node_name == "object">Id</#if> = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>" + DEFAULT_ORDER;
    <#if finder?parent?node_name == "object" && object.trashcan == "true">
    public static final String TRASH_BY_${finder?parent.@fieldName?upper_case} =
            "update `${object.@name}` set roDeleted = ? where ${finder?parent.@fieldName}<#if finder?parent?node_name == "object">Id</#if> = ?";
    </#if>
</#list>

<#list doc["//attributes/set/finder"] as finder>
    protected final static String FROM_${finder.@listName}_TABLE =
        " from `${object.@name}` as " + ALIAS + ", ${finder?parent.@relationTableName} as r where "
        <#if finder?parent.@pigsear == "true">
        + ALIAS + ".id = r.${finder?parent.@theirName}Id and r.${finder?parent.@myName}Id = ?"
        <#else>
        + ALIAS + ".id = r.${finder?parent.@myName}Id and r.${finder?parent.@theirName}Id = ?"
        </#if>
        <#if object.trashcan == "true">
        + " and " + ALIAS + ".roDeleted = false"
        </#if>
        <#if finder?parent.@sorted == "both" || finder?parent.@sorted == "me">
        + " order by r.${finder?parent.@myName}SortOrder," + ALIAS + ".sortOrder"
        </#if>;

    public final static String ${finder.@listName} =
        "select " + LIST_FIELDS + FROM_${finder.@listName}_TABLE;
    public final static String ${finder.@tableName} =
        "select " + TABLE_FIELDS + FROM_${finder.@listName}_TABLE;
</#list>

<#if object.lazyScope[0]??>
    public static final String FETCH_LAZY_BY_ID = 
        "select " + LAZY_FIELDS + " from `${object.@name}` as " + ALIAS + " where  " + ALIAS + ".id = ?<#if object.trashcan == "true"> and  " + ALIAS + ".roDeleted = false</#if>";
</#if>

    /** Create table statement for MySQL */
    public final static String CREATE_TABLE = "create table `${object.@name}` ("
        + "id bigint unsigned not null auto_increment, "
<#list object.formScope?children as att>
    <#if att?node_name != "object" && att?node_type == "element">
        + "${att.@fieldName} <#t>
        <#if att?node_name == "boolean">
        tinyint(1) <#t>
            <#if att.@default[0]??>
            default <#if att.@default == "true">1<#else>0</#if> <#t>
            </#if>
        <#elseif att?node_name == "date">
        date <#t>
        <#elseif att?node_name == "datetime">
        datetime <#t>
        <#elseif att?node_name == "enum">
        enum(<#t>
            <#list att.element as value>
        '${value.@name}'<#if value_has_next>,</#if><#t>
            </#list>
        ) <#t>
            <#if att.@default[0]??>
        default '${att.@default}' <#t>
            </#if>
        <#elseif att?node_name=="integer">
        int <#t>
            <#if att.@default[0]??>
        default ${att.@default} <#t>
            </#if>
        <#elseif att?node_name=="long">
        bigint <#t>
            <#if att.@parentClass[0]??>
        unsigned <#t>
            </#if>
            <#if att.@default[0]??>
            default ${att.@default} <#t>
            </#if>
        <#elseif att?node_name=="double">
        double <#t>
            <#if att.@default[0]??>
            default ${att.@default} <#t>
            </#if>
        <#elseif att?node_name=="string">
            <#if att.@maxlength?number &gt; 16000000>
        longtext <#t>
            <#elseif att.@maxlength?number &gt; 65000>
        mediumtext <#t>
            <#elseif att.@maxlength?number &gt; 255>
        text <#t> 
            <#else>
        varchar(${att.@maxlength}) <#t>
                <#if att.@default[0]??>
        default '${att.@default}' <#t>
                </#if>
            </#if>
        <#elseif att?node_name == "html">
        longtext <#t>
        </#if>
        <#if att.@notnull[0]?? && att.@notnull == "true">
        not null <#t>
        </#if>
        ,"<#t>

    </#if>
</#list>
<#if object.trashcan == "true">
        + "roDeleted boolean not null default false,"
</#if>
        + "creationDate timestamp default CURRENT_TIMESTAMP, updateDate timestamp null"
        + ", primary key (id)"
<#list doc["//attributes/*/finder[not(ancestor::set)]"] as finder>
    <#if finder?parent?node_name == "object">
        + ", foreign key ${object.@name}_${finder?parent.@fieldName}Id (${finder?parent.@fieldName}Id) references `${finder?parent.@class}`(id)<#if finder?parent.@cascade == 'true'> on delete cascade</#if>"
    <#else>
        + ", index ${object.@name}_${finder?parent.@fieldName} (${finder?parent.@fieldName})"
    </#if>
</#list>
        + ") default CHARSET=utf8 ENGINE INNODB";

<#list doc["/object/attributes/set[@multiplicity='n-to-m']"] as att>
    /** Create table of set statement for MySQL */
    public final static String CREATE_${att.@relationTableName?upper_case}_TABLE = "create table ${att.@relationTableName} ("
        + "${att.@myName}Id bigint unsigned not null"
        + ",${att.@theirName}Id bigint unsigned not null"
    <#if att.@sorted == "me" || att.@sorted == "both">
        + ",${att.@myName}SortOrder int unsigned"
    </#if>
    <#if att.@sorted == "them" || att.@sorted == "both">
        + ",${att.@theirName}SortOrder int"
    </#if>
    <#if object.trashcan == "true">
        + ",roDeleted boolean not null default false"
    </#if>
        + ",foreign key ${att.@relationTableName}_${att.@myName} (${att.@myName}Id) references `${object.@name}`(id) on delete cascade"
        + ",foreign key ${att.@relationTableName}_${att.@theirName} (${att.@theirName}Id) references `${att.@class}`(id) on delete cascade"
       + ") ENGINE INNODB";

    public final static String DELETE_${att.@relationTableName?upper_case}_RELATION =
        "delete from ${att.@relationTableName} where ${att.@myName}Id = ? and ${att.@theirName}Id = ?";

    <#if object.trashcan == "true">
    public final static String TRASH_${att.@relationTableName?upper_case}_RELATION = 
        "update ${att.@relationTableName} set roDeleted = true where ${att.@myName}Id = ? and ${att.@theirName}Id = ?";
    public final static String TRASH_${att.@relationTableName?upper_case}_RELATION_BY_${att.@myName?upper_case}_ID =
        "update ${att.@relationTableName} set roDeleted = ? where ${att.@myName}Id = ?";
    </#if>

    <#if att.@sorted == "both" || att.@sorted == "them">
    public final static String INSERT_${att.@relationTableName?upper_case}_RELATION =
        "insert into ${att.@relationTableName} (${att.@myName}Id,${att.@theirName}Id,${att.@theirName}SortOrder) values (?,?,?)";
    public final static String UPDATE_${att.@relationTableName?upper_case}_RELATION =
        "update ${att.@relationTableName} set ${att.@theirName}SortOrder = ? where ${att.@myName}Id = ? and ${att.@theirName}Id = ?";
    public final static String COUNT_${att.@relationTableName?upper_case}_RELATION =
        "select count(1) from ${att.@relationTableName} where ${att.@myName}Id = ?";
    <#else>
    public final static String INSERT_${att.@relationTableName?upper_case}_RELATION =
        "insert into ${att.@relationTableName} (${att.@myName}Id,${att.@theirName}Id) values (?,?)";
    </#if>

    <#if att.@sorted == "both" || att.@sorted == "me">
    public final static String UPDATE_${att.@relationTableName?upper_case}_SORTORDER =
        "update ${att.@relationTableName} set ${att.@myName}SortOrder = ? where ${att.@myName}Id = ? and ${att.@theirName}Id = ?";
    </#if>
</#list>
}
